<?php

namespace App\System\Component\Execl\Reader;

use Iterator;
use Countable;
use Exception;
use ZipArchive;
use DateTime;
use SimpleXMLElement;
use XMLReader;
use DateTimeZone;
use DateInterval;

/**
 * Class for parsing XLSX files specifically
 *
 * @author Martins Pilsetnieks
 */
class SpreadsheetReader_XLSX implements Iterator, Countable {
	const CELL_TYPE_BOOL = 'b';
	const CELL_TYPE_NUMBER = 'n';
	const CELL_TYPE_ERROR = 'e';
	const CELL_TYPE_SHARED_STR = 's';
	const CELL_TYPE_STR = 'str';
	const CELL_TYPE_INLINE_STR = 'inlineStr';
	
	/**
	 * Number of shared strings that can be reasonably cached, i.e., that aren't read from file but stored in memory.
	 * If the total number of shared strings is higher than this, caching is not used.
	 * If this value is null, shared strings are cached regardless of amount.
	 * With large shared string caches there are huge performance gains, however a lot of memory could be used which
	 * can be a problem, especially on shared hosting.
	 */
	const SHARED_STRING_CACHE_LIMIT = 50000;
	private $Options = array (
			'TempDir' => '',
			'ReturnDateTimeObjects' => false 
	);
	private static $RuntimeInfo = array (
			'GMPSupported' => false 
	);
	private $Valid = false;
	
	/**
	 *
	 * @var SpreadsheetReader_* Handle for the reader object
	 */
	private $Handle = false;
	
	// Worksheet file
	/**
	 *
	 * @var string Path to the worksheet XML file
	 */
	private $WorksheetPath = false;
	/**
	 *
	 * @var XMLReader XML reader object for the worksheet XML file
	 */
	private $Worksheet = false;
	
	// Shared strings file
	/**
	 *
	 * @var string Path to shared strings XML file
	 */
	private $SharedStringsPath = false;
	/**
	 *
	 * @var XMLReader XML reader object for the shared strings XML file
	 */
	private $SharedStrings = false;
	/**
	 *
	 * @var array Shared strings cache, if the number of shared strings is low enough
	 */
	private $SharedStringCache = array ();
	
	// Workbook data
	/**
	 *
	 * @var SimpleXMLElement XML object for the workbook XML file
	 */
	private $WorkbookXML = false;
	
	// Style data
	/**
	 *
	 * @var SimpleXMLElement XML object for the styles XML file
	 */
	private $StylesXML = false;
	/**
	 *
	 * @var array Container for cell value style data
	 */
	private $Styles = array ();
	private $TempDir = '';
	private $TempFiles = array ();
	private $CurrentRow = false;
	
	// Runtime parsing data
	/**
	 *
	 * @var int Current row in the file
	 */
	private $Index = 0;
	
	/**
	 *
	 * @var array Data about separate sheets in the file
	 */
	private $Sheets = false;
	private $SharedStringCount = 0;
	private $SharedStringIndex = 0;
	private $LastSharedStringValue = null;
	private $RowOpen = false;
	private $SSOpen = false;
	private $SSForwarded = false;
	private static $BuiltinFormats = array (
			0 => '',
			1 => '0',
			2 => '0.00',
			3 => '#,##0',
			4 => '#,##0.00',
			
			9 => '0%',
			10 => '0.00%',
			11 => '0.00E+00',
			12 => '# ?/?',
			13 => '# ??/??',
			14 => 'mm-dd-yy',
			15 => 'd-mmm-yy',
			16 => 'd-mmm',
			17 => 'mmm-yy',
			18 => 'h:mm AM/PM',
			19 => 'h:mm:ss AM/PM',
			20 => 'h:mm',
			21 => 'h:mm:ss',
			22 => 'm/d/yy h:mm',
			
			37 => '#,##0 ;(#,##0)',
			38 => '#,##0 ;[Red](#,##0)',
			39 => '#,##0.00;(#,##0.00)',
			40 => '#,##0.00;[Red](#,##0.00)',
			
			45 => 'mm:ss',
			46 => '[h]:mm:ss',
			47 => 'mmss.0',
			48 => '##0.0E+0',
			49 => '@',
			
			// CHT & CHS
			27 => '[$-404]e/m/d',
			30 => 'm/d/yy',
			36 => '[$-404]e/m/d',
			50 => '[$-404]e/m/d',
			57 => '[$-404]e/m/d',
			
			// THA
			59 => 't0',
			60 => 't0.00',
			61 => 't#,##0',
			62 => 't#,##0.00',
			67 => 't0%',
			68 => 't0.00%',
			69 => 't# ?/?',
			70 => 't# ??/??' 
	);
	private $Formats = array ();
	private static $DateReplacements = array (
			'All' => array (
					'\\' => '',
					'am/pm' => 'A',
					'yyyy' => 'Y',
					'yy' => 'y',
					'mmmmm' => 'M',
					'mmmm' => 'F',
					'mmm' => 'M',
					':mm' => ':i',
					'mm' => 'm',
					'm' => 'n',
					'dddd' => 'l',
					'ddd' => 'D',
					'dd' => 'd',
					'd' => 'j',
					'ss' => 's',
					'.s' => '' 
			),
			'24H' => array (
					'hh' => 'H',
					'h' => 'G' 
			),
			'12H' => array (
					'hh' => 'h',
					'h' => 'G' 
			) 
	);
	private static $BaseDate = false;
	private static $DecimalSeparator = '.';
	private static $ThousandSeparator = '';
	private static $CurrencyCode = '';
	
	/**
	 *
	 * @var array Cache for already processed format strings
	 */
	private $ParsedFormatCache = array ();
	
	/**
	 *
	 * @param
	 *        	string Path to file
	 * @param
	 *        	array Options:
	 *        	TempDir => string Temporary directory path
	 *        	ReturnDateTimeObjects => bool True => dates and times will be returned as PHP DateTime objects, false => as strings
	 */
	public function __construct($Filepath, array $Options = null) {
		if (! is_readable ( $Filepath )) {
			throw new Exception ( 'SpreadsheetReader_XLSX: File not readable (' . $Filepath . ')' );
		}
		
		$this->TempDir = isset ( $Options ['TempDir'] ) && is_writable ( $Options ['TempDir'] ) ? $Options ['TempDir'] : sys_get_temp_dir ();
		
		$this->TempDir = rtrim ( $this->TempDir, DIRECTORY_SEPARATOR );
		$this->TempDir = $this->TempDir . DIRECTORY_SEPARATOR . uniqid () . DIRECTORY_SEPARATOR;
		
		$Zip = new ZipArchive ();
		$Status = $Zip->open ( $Filepath );
		
		if ($Status !== true) {
			throw new Exception ( 'SpreadsheetReader_XLSX: File not readable (' . $Filepath . ') (Error ' . $Status . ')' );
		}
		
		// Getting the general workbook information
		if ($Zip->locateName ( 'xl/workbook.xml' ) !== false) {
			$this->WorkbookXML = new SimpleXMLElement ( $Zip->getFromName ( 'xl/workbook.xml' ) );
		}
		
		// Extracting the XMLs from the XLSX zip file
		if ($Zip->locateName ( 'xl/sharedStrings.xml' ) !== false) {
			$this->SharedStringsPath = $this->TempDir . 'xl' . DIRECTORY_SEPARATOR . 'sharedStrings.xml';
			$Zip->extractTo ( $this->TempDir, 'xl/sharedStrings.xml' );
			$this->TempFiles [] = $this->TempDir . 'xl' . DIRECTORY_SEPARATOR . 'sharedStrings.xml';
			
			if (is_readable ( $this->SharedStringsPath )) {
				$this->SharedStrings = new XMLReader ();
				$this->SharedStrings->open ( $this->SharedStringsPath );
				$this->PrepareSharedStringCache ();
			}
		}
		
		$Sheets = $this->Sheets ();
		
		foreach ( $this->Sheets as $Index => $Name ) {
			if ($Zip->locateName ( 'xl/worksheets/sheet' . $Index . '.xml' ) !== false) {
				$Zip->extractTo ( $this->TempDir, 'xl/worksheets/sheet' . $Index . '.xml' );
				$this->TempFiles [] = $this->TempDir . 'xl' . DIRECTORY_SEPARATOR . 'worksheets' . DIRECTORY_SEPARATOR . 'sheet' . $Index . '.xml';
			}
		}
		
		$this->ChangeSheet ( 0 );
		
		// If worksheet is present and is OK, parse the styles already
		if ($Zip->locateName ( 'xl/styles.xml' ) !== false) {
			$this->StylesXML = new SimpleXMLElement ( $Zip->getFromName ( 'xl/styles.xml' ) );
			if ($this->StylesXML && $this->StylesXML->cellXfs && $this->StylesXML->cellXfs->xf) {
				foreach ( $this->StylesXML->cellXfs->xf as $Index => $XF ) {
					// Format #0 is a special case - it is the "General" format that is applied regardless of applyNumberFormat
					if ($XF->attributes ()->applyNumberFormat || (0 == ( int ) $XF->attributes ()->numFmtId)) {
						$FormatId = ( int ) $XF->attributes ()->numFmtId;
						// If format ID >= 164, it is a custom format and should be read from styleSheet\numFmts
						$this->Styles [] = $FormatId;
					} else {
						// 0 for "General" format
						$this->Styles [] = 0;
					}
				}
			}
			
			if ($this->StylesXML->numFmts && $this->StylesXML->numFmts->numFmt) {
				foreach ( $this->StylesXML->numFmts->numFmt as $Index => $NumFmt ) {
					$this->Formats [( int ) $NumFmt->attributes ()->numFmtId] = ( string ) $NumFmt->attributes ()->formatCode;
				}
			}
			
			unset ( $this->StylesXML );
		}
		
		$Zip->close ();
		
		// Setting base date
		if (! self::$BaseDate) {
			self::$BaseDate = new DateTime ();
			self::$BaseDate->setTimezone ( new DateTimeZone ( 'UTC' ) );
			self::$BaseDate->setDate ( 1900, 1, 0 );
			self::$BaseDate->setTime ( 0, 0, 0 );
		}
		
		// Decimal and thousand separators
		if (! self::$DecimalSeparator && ! self::$ThousandSeparator && ! self::$CurrencyCode) {
			$Locale = localeconv ();
			self::$DecimalSeparator = $Locale ['decimal_point'];
			self::$ThousandSeparator = $Locale ['thousands_sep'];
			self::$CurrencyCode = $Locale ['int_curr_symbol'];
		}
		
		if (function_exists ( 'gmp_gcd' )) {
			self::$RuntimeInfo ['GMPSupported'] = true;
		}
	}
	
	/**
	 * Destructor, destroys all that remains (closes and deletes temp files)
	 */
	public function __destruct() {
		foreach ( $this->TempFiles as $TempFile ) {
			@unlink ( $TempFile );
		}
		
		// Better safe than sorry - shouldn't try deleting '.' or '/', or '..'.
		if (strlen ( $this->TempDir ) > 2) {
			@rmdir ( $this->TempDir . 'xl' . DIRECTORY_SEPARATOR . 'worksheets' );
			@rmdir ( $this->TempDir . 'xl' );
			@rmdir ( $this->TempDir );
		}
		
		if ($this->Worksheet && $this->Worksheet instanceof XMLReader) {
			$this->Worksheet->close ();
			unset ( $this->Worksheet );
		}
		unset ( $this->WorksheetPath );
		
		if ($this->SharedStrings && $this->SharedStrings instanceof XMLReader) {
			$this->SharedStrings->close ();
			unset ( $this->SharedStrings );
		}
		unset ( $this->SharedStringsPath );
		
		if (isset ( $this->StylesXML )) {
			unset ( $this->StylesXML );
		}
		if ($this->WorkbookXML) {
			unset ( $this->WorkbookXML );
		}
	}
	
	/**
	 * Retrieves an array with information about sheets in the current file
	 *
	 * @return array List of sheets (key is sheet index, value is name)
	 */
	public function Sheets() {
		if ($this->Sheets === false) {
			$this->Sheets = array ();
			foreach ( $this->WorkbookXML->sheets->sheet as $Index => $Sheet ) {
				$Attributes = $Sheet->attributes ( 'r', true );
				foreach ( $Attributes as $Name => $Value ) {
					if ($Name == 'id') {
						$SheetID = ( int ) str_replace ( 'rId', '', ( string ) $Value );
						break;
					}
				}
				
				$this->Sheets [$SheetID] = ( string ) $Sheet ['name'];
			}
			ksort ( $this->Sheets );
		}
		return array_values ( $this->Sheets );
	}
	
	/**
	 * Changes the current sheet in the file to another
	 *
	 * @param
	 *        	int Sheet index
	 *        	
	 * @return bool True if sheet was successfully changed, false otherwise.
	 */
	public function ChangeSheet($Index) {
		$RealSheetIndex = false;
		$Sheets = $this->Sheets ();
		if (isset ( $Sheets [$Index] )) {
			$SheetIndexes = array_keys ( $this->Sheets );
			$RealSheetIndex = $SheetIndexes [$Index];
		}
		
		$TempWorksheetPath = $this->TempDir . 'xl/worksheets/sheet' . $RealSheetIndex . '.xml';
		
		if ($RealSheetIndex !== false && is_readable ( $TempWorksheetPath )) {
			$this->WorksheetPath = $TempWorksheetPath;
			
			$this->rewind ();
			return true;
		}
		
		return false;
	}
	
	/**
	 * Creating shared string cache if the number of shared strings is acceptably low (or there is no limit on the amount
	 */
	private function PrepareSharedStringCache() {
		while ( $this->SharedStrings->read () ) {
			if ($this->SharedStrings->name == 'sst') {
				$this->SharedStringCount = $this->SharedStrings->getAttribute ( 'count' );
				break;
			}
		}
		
		if (! $this->SharedStringCount || (self::SHARED_STRING_CACHE_LIMIT < $this->SharedStringCount && self::SHARED_STRING_CACHE_LIMIT !== null)) {
			return false;
		}
		
		$CacheIndex = 0;
		$CacheValue = '';
		while ( $this->SharedStrings->read () ) {
			switch ($this->SharedStrings->name) {
				case 'si' :
					if ($this->SharedStrings->nodeType == XMLReader::END_ELEMENT) {
						$this->SharedStringCache [$CacheIndex] = $CacheValue;
						$CacheIndex ++;
						$CacheValue = '';
					}
					break;
				case 't' :
					if ($this->SharedStrings->nodeType == XMLReader::END_ELEMENT) {
						continue;
					}
					$CacheValue .= $this->SharedStrings->readString ();
					break;
			}
		}
		
		$this->SharedStrings->close ();
		return true;
	}
	
	/**
	 * Retrieves a shared string value by its index
	 *
	 * @param
	 *        	int Shared string index
	 *        	
	 * @return string Value
	 */
	private function GetSharedString($Index) {
		if ((self::SHARED_STRING_CACHE_LIMIT === null || self::SHARED_STRING_CACHE_LIMIT > 0) && ! empty ( $this->SharedStringCache )) {
			if (isset ( $this->SharedStringCache [$Index] )) {
				return $this->SharedStringCache [$Index];
			} else {
				return '';
			}
		}
		
		// If the desired index is before the current, rewind the XML
		if ($this->SharedStringIndex > $Index) {
			$this->SSOpen = false;
			$this->SharedStrings->close ();
			$this->SharedStrings->open ( $this->SharedStringsPath );
			$this->SharedStringIndex = 0;
			$this->LastSharedStringValue = null;
			$this->SSForwarded = false;
		}
		
		// Finding the unique string count (if not already read)
		if ($this->SharedStringIndex == 0 && ! $this->SharedStringCount) {
			while ( $this->SharedStrings->read () ) {
				if ($this->SharedStrings->name == 'sst') {
					$this->SharedStringCount = $this->SharedStrings->getAttribute ( 'uniqueCount' );
					break;
				}
			}
		}
		
		// If index of the desired string is larger than possible, don't even bother.
		if ($this->SharedStringCount && ($Index >= $this->SharedStringCount)) {
			return '';
		}
		
		// If an index with the same value as the last already fetched is requested
		// (any further traversing the tree would get us further away from the node)
		if (($Index == $this->SharedStringIndex) && ($this->LastSharedStringValue !== null)) {
			return $this->LastSharedStringValue;
		}
		
		// Find the correct <si> node with the desired index
		while ( $this->SharedStringIndex <= $Index ) {
			// SSForwarded is set further to avoid double reading in case nodes are skipped.
			if ($this->SSForwarded) {
				$this->SSForwarded = false;
			} else {
				$ReadStatus = $this->SharedStrings->read ();
				if (! $ReadStatus) {
					break;
				}
			}
			
			if ($this->SharedStrings->name == 'si') {
				if ($this->SharedStrings->nodeType == XMLReader::END_ELEMENT) {
					$this->SSOpen = false;
					$this->SharedStringIndex ++;
				} else {
					$this->SSOpen = true;
					
					if ($this->SharedStringIndex < $Index) {
						$this->SSOpen = false;
						$this->SharedStrings->next ( 'si' );
						$this->SSForwarded = true;
						$this->SharedStringIndex ++;
						continue;
					} else {
						break;
					}
				}
			}
		}
		
		$Value = '';
		
		// Extract the value from the shared string
		if ($this->SSOpen && ($this->SharedStringIndex == $Index)) {
			while ( $this->SharedStrings->read () ) {
				switch ($this->SharedStrings->name) {
					case 't' :
						if ($this->SharedStrings->nodeType == XMLReader::END_ELEMENT) {
							continue;
						}
						$Value .= $this->SharedStrings->readString ();
						break;
					case 'si' :
						if ($this->SharedStrings->nodeType == XMLReader::END_ELEMENT) {
							$this->SSOpen = false;
							$this->SSForwarded = true;
							break 2;
						}
						break;
				}
			}
		}
		
		if ($Value) {
			$this->LastSharedStringValue = $Value;
		}
		return $Value;
	}
	
	/**
	 * Formats the value according to the index
	 *
	 * @param
	 *        	string Cell value
	 * @param
	 *        	int Format index
	 *        	
	 * @return string Formatted cell value
	 */
	private function FormatValue($Value, $Index) {
		if (! is_numeric ( $Value )) {
			return $Value;
		}
		
		if (isset ( $this->Styles [$Index] ) && ($this->Styles [$Index] !== false)) {
			$Index = $this->Styles [$Index];
		} else {
			return $Value;
		}
		
		// A special case for the "General" format
		if ($Index == 0) {
			return $this->GeneralFormat ( $Value );
		}
		
		$Format = array ();
		
		if (isset ( $this->ParsedFormatCache [$Index] )) {
			$Format = $this->ParsedFormatCache [$Index];
		}
		
		if (! $Format) {
			$Format = array (
					'Code' => false,
					'Type' => false,
					'Scale' => 1,
					'Thousands' => false,
					'Currency' => false 
			);
			
			if (isset ( self::$BuiltinFormats [$Index] )) {
				$Format ['Code'] = self::$BuiltinFormats [$Index];
			} elseif (isset ( $this->Formats [$Index] )) {
				$Format ['Code'] = $this->Formats [$Index];
			}
			
			// Format code found, now parsing the format
			if ($Format ['Code']) {
				$Sections = explode ( ';', $Format ['Code'] );
				$Format ['Code'] = $Sections [0];
				
				switch (count ( $Sections )) {
					case 2 :
						if ($Value < 0) {
							$Format ['Code'] = $Sections [1];
						}
						break;
					case 3 :
					case 4 :
						if ($Value < 0) {
							$Format ['Code'] = $Sections [1];
						} elseif ($Value == 0) {
							$Format ['Code'] = $Sections [2];
						}
						break;
				}
			}
			
			// Stripping colors
			$Format ['Code'] = trim ( preg_replace ( '{^\[[[:alpha:]]+\]}i', '', $Format ['Code'] ) );
			
			// Percentages
			if (substr ( $Format ['Code'], - 1 ) == '%') {
				$Format ['Type'] = 'Percentage';
			} elseif (preg_match ( '{^(\[\$[[:alpha:]]*-[0-9A-F]*\])*[hmsdy]}i', $Format ['Code'] )) {
				$Format ['Type'] = 'DateTime';
				
				$Format ['Code'] = trim ( preg_replace ( '{^(\[\$[[:alpha:]]*-[0-9A-F]*\])}i', '', $Format ['Code'] ) );
				$Format ['Code'] = strtolower ( $Format ['Code'] );
				
				$Format ['Code'] = strtr ( $Format ['Code'], self::$DateReplacements ['All'] );
				if (strpos ( $Format ['Code'], 'A' ) === false) {
					$Format ['Code'] = strtr ( $Format ['Code'], self::$DateReplacements ['24H'] );
				} else {
					$Format ['Code'] = strtr ( $Format ['Code'], self::$DateReplacements ['12H'] );
				}
			} elseif ($Format ['Code'] == '[$EUR ]#,##0.00_-') {
				$Format ['Type'] = 'Euro';
			} else {
				// Removing skipped characters
				$Format ['Code'] = preg_replace ( '{_.}', '', $Format ['Code'] );
				// Removing unnecessary escaping
				$Format ['Code'] = preg_replace ( "{\\\\}", '', $Format ['Code'] );
				// Removing string quotes
				$Format ['Code'] = str_replace ( array (
						'"',
						'*' 
				), '', $Format ['Code'] );
				// Removing thousands separator
				if (strpos ( $Format ['Code'], '0,0' ) !== false || strpos ( $Format ['Code'], '#,#' ) !== false) {
					$Format ['Thousands'] = true;
				}
				$Format ['Code'] = str_replace ( array (
						'0,0',
						'#,#' 
				), array (
						'00',
						'##' 
				), $Format ['Code'] );
				
				// Scaling (Commas indicate the power)
				$Scale = 1;
				$Matches = array ();
				if (preg_match ( '{(0|#)(,+)}', $Format ['Code'], $Matches )) {
					$Scale = pow ( 1000, strlen ( $Matches [2] ) );
					// Removing the commas
					$Format ['Code'] = preg_replace ( array (
							'{0,+}',
							'{#,+}' 
					), array (
							'0',
							'#' 
					), $Format ['Code'] );
				}
				
				$Format ['Scale'] = $Scale;
				
				if (preg_match ( '{#?.*\?\/\?}', $Format ['Code'] )) {
					$Format ['Type'] = 'Fraction';
				} else {
					$Format ['Code'] = str_replace ( '#', '', $Format ['Code'] );
					
					$Matches = array ();
					if (preg_match ( '{(0+)(\.?)(0*)}', preg_replace ( '{\[[^\]]+\]}', '', $Format ['Code'] ), $Matches )) {
						$Integer = $Matches [1];
						$DecimalPoint = $Matches [2];
						$Decimals = $Matches [3];
						
						$Format ['MinWidth'] = strlen ( $Integer ) + strlen ( $DecimalPoint ) + strlen ( $Decimals );
						$Format ['Decimals'] = $Decimals;
						$Format ['Precision'] = strlen ( $Format ['Decimals'] );
						$Format ['Pattern'] = '%0' . $Format ['MinWidth'] . '.' . $Format ['Precision'] . 'f';
					}
				}
				
				$Matches = array ();
				if (preg_match ( '{\[\$(.*)\]}u', $Format ['Code'], $Matches )) {
					$CurrFormat = $Matches [0];
					$CurrCode = $Matches [1];
					$CurrCode = explode ( '-', $CurrCode );
					if ($CurrCode) {
						$CurrCode = $CurrCode [0];
					}
					
					if (! $CurrCode) {
						$CurrCode = self::$CurrencyCode;
					}
					
					$Format ['Currency'] = $CurrCode;
				}
				$Format ['Code'] = trim ( $Format ['Code'] );
			}
			
			$this->ParsedFormatCache [$Index] = $Format;
		}
		
		// Applying format to value
		if ($Format) {
			if ($Format ['Code'] == '@') {
				return ( string ) $Value;
			} // Percentages
elseif ($Format ['Type'] == 'Percentage') {
				if ($Format ['Code'] === '0%') {
					$Value = round ( 100 * $Value, 0 ) . '%';
				} else {
					$Value = sprintf ( '%.2f%%', round ( 100 * $Value, 2 ) );
				}
			} // Dates and times
elseif ($Format ['Type'] == 'DateTime') {
				$Days = ( int ) $Value;
				// Correcting for Feb 29, 1900
				if ($Days > 60) {
					$Days --;
				}
				
				// At this point time is a fraction of a day
				$Time = ($Value - ( int ) $Value);
				$Seconds = 0;
				if ($Time) {
					// Here time is converted to seconds
					// Some loss of precision will occur
					$Seconds = ( int ) ($Time * 86400);
				}
				
				$Value = clone self::$BaseDate;
				$Value->add ( new DateInterval ( 'P' . $Days . 'D' . ($Seconds ? 'T' . $Seconds . 'S' : '') ) );
				
				if (! $this->Options ['ReturnDateTimeObjects']) {
					$Value = $Value->format ( $Format ['Code'] );
				} else {
					// A DateTime object is returned
				}
			} elseif ($Format ['Type'] == 'Euro') {
				$Value = 'EUR ' . sprintf ( '%1.2f', $Value );
			} else {
				// Fractional numbers
				if ($Format ['Type'] == 'Fraction' && ($Value != ( int ) $Value)) {
					$Integer = floor ( abs ( $Value ) );
					$Decimal = fmod ( abs ( $Value ), 1 );
					// Removing the integer part and decimal point
					$Decimal *= pow ( 10, strlen ( $Decimal ) - 2 );
					$DecimalDivisor = pow ( 10, strlen ( $Decimal ) );
					
					if (self::$RuntimeInfo ['GMPSupported']) {
						$GCD = gmp_strval ( gmp_gcd ( $Decimal, $DecimalDivisor ) );
					} else {
						$GCD = self::GCD ( $Decimal, $DecimalDivisor );
					}
					
					$DecimalPart = null;
					$AdjDecimal = $DecimalPart / $GCD;
					$AdjDecimalDivisor = $DecimalDivisor / $GCD;
					
					if (strpos ( $Format ['Code'], '0' ) !== false || strpos ( $Format ['Code'], '#' ) !== false || substr ( $Format ['Code'], 0, 3 ) == '? ?') {
						// The integer part is shown separately apart from the fraction
						$Value = ($Value < 0 ? '-' : '') . $Integer ? $Integer . ' ' : '' . $AdjDecimal . '/' . $AdjDecimalDivisor;
					} else {
						// The fraction includes the integer part
						$AdjDecimal += $Integer * $AdjDecimalDivisor;
						$Value = ($Value < 0 ? '-' : '') . $AdjDecimal . '/' . $AdjDecimalDivisor;
					}
				} else {
					// Scaling
					$Value = $Value / $Format ['Scale'];
					
					if (! empty ( $Format ['MinWidth'] ) && $Format ['Decimals']) {
						if ($Format ['Thousands']) {
							$Value = number_format ( $Value, $Format ['Precision'], self::$DecimalSeparator, self::$ThousandSeparator );
						} else {
							$Value = sprintf ( $Format ['Pattern'], $Value );
						}
						
						$Value = preg_replace ( '{(0+)(\.?)(0*)}', $Value, $Format ['Code'] );
					}
				}
				
				// Currency/Accounting
				if ($Format ['Currency']) {
					$Value = preg_replace ( '', $Format ['Currency'], $Value );
				}
			}
		}
		
		return $Value;
	}
	
	/**
	 * Attempts to approximate Excel's "general" format.
	 *
	 * @param
	 *        	mixed Value
	 *        	
	 * @return mixed Result
	 */
	public function GeneralFormat($Value) {
		// Numeric format
		if (is_numeric ( $Value )) {
			$Value = ( float ) $Value;
		}
		return $Value;
	}
	
	// !Iterator interface methods
	/**
	 * Rewind the Iterator to the first element.
	 * Similar to the reset() function for arrays in PHP
	 */
	public function rewind() {
		// Removed the check whether $this -> Index == 0 otherwise ChangeSheet doesn't work properly
		
		// If the worksheet was already iterated, XML file is reopened.
		// Otherwise it should be at the beginning anyway
		if ($this->Worksheet instanceof XMLReader) {
			$this->Worksheet->close ();
		} else {
			$this->Worksheet = new XMLReader ();
		}
		
		$this->Worksheet->open ( $this->WorksheetPath );
		
		$this->Valid = true;
		$this->RowOpen = false;
		$this->CurrentRow = false;
		$this->Index = 0;
	}
	
	/**
	 * Return the current element.
	 * Similar to the current() function for arrays in PHP
	 *
	 * @return mixed current element from the collection
	 */
	public function current() {
		if ($this->Index == 0 && $this->CurrentRow === false) {
			$this->next ();
			$this->Index --;
		}
		return $this->CurrentRow;
	}
	
	/**
	 * Move forward to next element.
	 * Similar to the next() function for arrays in PHP
	 */
	public function next() {
		$this->Index ++;
		
		$this->CurrentRow = array ();
		
		if (! $this->RowOpen) {
			while ( $this->Valid = $this->Worksheet->read () ) {
				if ($this->Worksheet->name == 'row') {
					// Getting the row spanning area (stored as e.g., 1:12)
					// so that the last cells will be present, even if empty
					$RowSpans = $this->Worksheet->getAttribute ( 'spans' );
					if ($RowSpans) {
						$RowSpans = explode ( ':', $RowSpans );
						$CurrentRowColumnCount = $RowSpans [1];
					} else {
						$CurrentRowColumnCount = 0;
					}
					
					if ($CurrentRowColumnCount > 0) {
						$this->CurrentRow = array_fill ( 0, $CurrentRowColumnCount, '' );
					}
					
					$this->RowOpen = true;
					break;
				}
			}
		}
		
		// Reading the necessary row, if found
		if ($this->RowOpen) {
			// These two are needed to control for empty cells
			$MaxIndex = 0;
			$CellCount = 0;
			
			$CellHasSharedString = false;
			
			while ( $this->Valid = $this->Worksheet->read () ) {
				switch ($this->Worksheet->name) {
					// End of row
					case 'row' :
						if ($this->Worksheet->nodeType == XMLReader::END_ELEMENT) {
							$this->RowOpen = false;
							break 2;
						}
						break;
					// Cell
					case 'c' :
						// If it is a closing tag, skip it
						if ($this->Worksheet->nodeType == XMLReader::END_ELEMENT) {
							continue;
						}
						
						$StyleId = ( int ) $this->Worksheet->getAttribute ( 's' );
						
						// Get the index of the cell
						$Index = $this->Worksheet->getAttribute ( 'r' );
						$Letter = preg_replace ( '{[^[:alpha:]]}S', '', $Index );
						$Index = self::IndexFromColumnLetter ( $Letter );
						
						// Determine cell type
						if ($this->Worksheet->getAttribute ( 't' ) == self::CELL_TYPE_SHARED_STR) {
							$CellHasSharedString = true;
						} else {
							$CellHasSharedString = false;
						}
						
						$this->CurrentRow [$Index] = '';
						
						$CellCount ++;
						if ($Index > $MaxIndex) {
							$MaxIndex = $Index;
						}
						
						break;
					// Cell value
					case 'v' :
					case 'is' :
						if ($this->Worksheet->nodeType == XMLReader::END_ELEMENT) {
							continue;
						}
						
						$Value = $this->Worksheet->readString ();
						
						if ($CellHasSharedString) {
							$Value = $this->GetSharedString ( $Value );
						}
						
						// Format value if necessary
						if ($Value !== '' && $StyleId && isset ( $this->Styles [$StyleId] )) {
							$Value = $this->FormatValue ( $Value, $StyleId );
						} elseif ($Value) {
							$Value = $this->GeneralFormat ( $Value );
						}
						
						$this->CurrentRow [$Index] = $Value;
						break;
				}
			}
			
			// Adding empty cells, if necessary
			// Only empty cells inbetween and on the left side are added
			if ($MaxIndex + 1 > $CellCount) {
				$this->CurrentRow = $this->CurrentRow + array_fill ( 0, $MaxIndex + 1, '' );
				ksort ( $this->CurrentRow );
			}
		}
		
		return $this->CurrentRow;
	}
	
	/**
	 * Return the identifying key of the current element.
	 * Similar to the key() function for arrays in PHP
	 *
	 * @return mixed either an integer or a string
	 */
	public function key() {
		return $this->Index;
	}
	
	/**
	 * Check if there is a current element after calls to rewind() or next().
	 * Used to check if we've iterated to the end of the collection
	 *
	 * @return boolean FALSE if there's nothing more to iterate over
	 */
	public function valid() {
		return $this->Valid;
	}
	
	// !Countable interface method
	/**
	 * Ostensibly should return the count of the contained items but this just returns the number
	 * of rows read so far.
	 * It's not really correct but at least coherent.
	 */
	public function count() {
		return $this->Index + 1;
	}
	
	/**
	 * Takes the column letter and converts it to a numerical index (0-based)
	 *
	 * @param
	 *        	string Letter(s) to convert
	 *        	
	 * @return mixed Numeric index (0-based) or boolean false if it cannot be calculated
	 */
	public static function IndexFromColumnLetter($Letter) {
		$Powers = array ();
		
		$Letter = strtoupper ( $Letter );
		
		$Result = 0;
		for($i = strlen ( $Letter ) - 1, $j = 0; $i >= 0; $i --, $j ++) {
			$Ord = ord ( $Letter [$i] ) - 64;
			if ($Ord > 26) {
				// Something is very, very wrong
				return false;
			}
			$Result += $Ord * pow ( 26, $j );
		}
		return $Result - 1;
	}
	
	/**
	 * Helper function for greatest common divisor calculation in case GMP extension is
	 * not enabled
	 *
	 * @param
	 *        	int Number #1
	 * @param
	 *        	int Number #2
	 *        	
	 * @param
	 *        	int Greatest common divisor
	 */
	public static function GCD($A, $B) {
		$A = abs ( $A );
		$B = abs ( $B );
		if ($A + $B == 0) {
			return 0;
		} else {
			$C = 1;
			
			while ( $A > 0 ) {
				$C = $A;
				$A = $B % $A;
				$B = $C;
			}
			
			return $C;
		}
	}
}
?>
